﻿using SqlSugar;
using System.Linq.Expressions;

namespace Util
{
    public class SqlSugarHelper<T> where T : class, new()
    {
        //注意：不能写成静态的
        private readonly ISqlSugarClient _db;
        //public SimpleClient<T> CurrentDb { get { return new SimpleClient<T>(Db); } }//用来操作当前表的数据

        public SqlSugarHelper(ISqlSugarClient db)
        {
            _db = db ?? throw new ArgumentNullException(nameof(db));

            // 调试代码 用来打印SQL 
            //_db.Aop.OnLogExecuting = (sql, pars) =>
            //{
            //    Console.WriteLine(sql + "\r\n" +
            //        _db.Utilities.SerializeObject(pars.ToDictionary(it => it.ParameterName, it => it.Value)));
            //};
        }

        public SimpleClient<T> CurrentDb { get { return new SimpleClient<T>(_db); } } // 用来操作当前表的数据

        /// <summary>
        /// 写入实体数据
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public async Task<bool> Add(T model)
        {
            var i = await Task.Run(() => _db.Insertable(model).ExecuteReturnBigIdentity());
            //返回的i是long类型,这里你可以根据你的业务需要进行处理
            return i > 0;
        }

        /// <summary>
        /// 写入实体数据
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public async Task<int> AddBackID(T model)
        {
            return await Task.Run(() => _db.Insertable(model).ExecuteReturnIdentity());
        }

        /// <summary>
        /// 根据ID删除
        /// </summary>
        /// <param name="ids"></param>
        /// <returns></returns>
        public async Task<bool> DeleteByIds(object[] ids)
        {
            var i = await Task.Run(() => _db.Deleteable<T>().In(ids).ExecuteCommand());
            return i > 0;
        }

        /// <summary>
        /// 根据ID查询一条数据
        /// </summary>
        /// <param name="objId"></param>
        /// <returns></returns>
        public async Task<T> QueryByID(object objId)
        {
            return await Task.Run(() => _db.Queryable<T>().InSingle(objId));
        }

        /// <summary>
        /// 更新实体数据
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public async Task<bool> Update(T model)
        {
            //这种方式会以主键为条件
            var i = await Task.Run(() => _db.Updateable(model).ExecuteCommand());
            return i > 0;
        }

        public async Task<T> QueryByWhere(Expression<Func<T, bool>> expression)
        {
            return await Task.Run(() => _db.Queryable<T>().Where(expression).First());
        }

        /// <summary>
        /// 获取正序数据集合
        /// </summary>
        /// <param name="expression"></param>
        /// <returns></returns>
        public async Task<dynamic> GetListByAsc(Expression<Func<T, bool>> expression, Expression<Func<T, object>> order)
        {
            return await Task.Run(() => _db.Queryable<T>().Where(expression).OrderBy(order).ToList());
        }

        /// <summary>
        /// 获取反序数据集合
        /// </summary>
        /// <param name="expression"></param>
        /// <returns></returns>
        public async Task<dynamic> GetListByDesc(Expression<Func<T, bool>> expression, Expression<Func<T, object>> order)
        {
            return await Task.Run(() => _db.Queryable<T>().Where(expression).OrderBy(order, OrderByType.Desc).ToList());
        }

        /// <summary>
        /// 分页获取正序数据集合
        /// </summary>
        /// <param name="select"></param>
        /// <param name="where"></param>
        /// <param name="order"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="Total"></param>
        /// <returns></returns>
        public async Task<dynamic> GetPageListByAsc(Expression<Func<T, dynamic>> select, Expression<Func<T, bool>> where, Expression<Func<T, object>> order, int pageIndex, int pageSize)
        {
            return await Task.Run(() => _db.Queryable<T>().Where(where).OrderBy(order).Select(select).Skip((pageIndex - 1) * pageSize).Take(pageSize));
        }

        /// <summary>
        /// 分页获取反序数据集合
        /// </summary>
        /// <param name="select"></param>
        /// <param name="where"></param>
        /// <param name="order"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public async Task<dynamic> GetPageListByDesc(Expression<Func<T, dynamic>> select, Expression<Func<T, bool>> where, Expression<Func<T, object>> order, int pageIndex, int pageSize)
        {
            return await Task.Run(() => _db.Queryable<T>().Where(where).OrderBy(order, OrderByType.Desc).Select(select).Skip((pageIndex - 1) * pageSize).Take(pageSize));
        }

        /// <summary>
        /// 获取数据总行数
        /// </summary>
        /// <param name="where"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public async Task<int> GetTotal(Expression<Func<T, bool>> where)
        {
            return await Task.Run(() => _db.Queryable<T>().Where(where).ToList().Count);
        }

        /// <summary>
        /// 获取数据总页数
        /// </summary>
        /// <param name="where"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public async Task<int> GetPageTotle(Expression<Func<T, bool>> where, int pageSize)
        {
            int total = await Task.Run(() => _db.Queryable<T>().Where(where).ToList().Count);
            if (total % pageSize > 0)
                total = total / pageSize + 1;
            else
                total = total / pageSize;
            return total;
        }

        /// <summary>
        /// 执行SQL查询
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public async Task<dynamic> SqlQuery(string sql)
        {
            return await Task.Run(() => _db.Queryable<dynamic>(sql));
        }
    }
}
